/**
 * Created by mengwei666 on 2016/11/5.
 */

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBOpener {
    public static final String url = "jdbc:mysql://127.0.0.1/videos?useSSL=true";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "1024";
    public static final String[] categories={"MAD·AMV","MMD·3D","短片·手书·配音","综合","连载动画","完结动画","国产动画","资讯","官方延伸","新番时间表","番剧索引",
    "原创音乐","翻唱","VOCALOID·UTAU","演奏","三次元音乐","OP/ED/OST","音乐选集","宅舞","三次元舞蹈","舞蹈教程","单机联机","网游·电竞","音游","Mugen",
    "GMV","纪录片","趣味科普人文","野生技术协会","演讲·公开课","星海","数码","机械","搞笑","日常","美食圈","动物圈","手工","绘画","运动","鬼畜调教","音MAD","人力VOCALOID",
    "教程演示","美妆","服饰","健身","资讯","广告","综艺","明星","Korea相关"};

    public Connection conn = null;
    public PreparedStatement pst = null;

    private Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(name); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    private void close() {
        try {
            this.conn.close();
            this.pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int insert(String av_number,String author,int favorite,String category) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into bilibili(av_number,author,favorite,category) values(?,?,?,?)";
        try {
            pst = (PreparedStatement) conn.prepareStatement(sql);
            pst.setString(1,av_number);
            pst.setString(2,author);
            pst.setInt(3,favorite);
            pst.setString(4,category);
            i += pst.executeUpdate();
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            pst = null;
            conn = null;
        }
        return i;
    }

    //将每个分类的前三名保存到List
    public List sort(){
        List list=new ArrayList();
        try {
        Connection conn = getConn();
        String sql;
        for(int i=0;i<categories.length;i++){
        sql = "select * from bilibili where category='"+categories[i]+"' order by favorite desc limit 3";
            pst = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next())
                list.add("http://www.ibilibili.com/video/av" + rs.getString(1) + "/");
            pst.close();
        }
            conn.close();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            pst = null;
            conn = null;
        }
        return list;
    }
}
